//
//  DatabaseHandler.m
//  UI12_网络电影解析作业
//
//  Created by cuixin on 15/9/23.
//  Copyright © 2015年 dllo. All rights reserved.
//

#import "DatabaseHandler.h"

@implementation DatabaseHandler

+(DatabaseHandler *)shareDatabaseHandler{
    static DatabaseHandler *handler = nil;
    @synchronized(self) {
        if (handler == nil) {
            handler = [[DatabaseHandler alloc] init];
        }
    }
    
    return handler;
}

static sqlite3 *db;

-(void)openDB{
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    NSString *databasePath = [path stringByAppendingPathComponent:@"detail1.sqlite"];
    
    NSLog(@"%@", databasePath);

    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    //打开数据库 ，使用sqlite3_open
    //参数说明： 1.数据库打开的位置 2.数据库对象 3.返回值：该方法的执行结果（是个枚举类型 sqlite_ok，打开成功）
    
    int result = sqlite3_open(databasePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else
    {
        NSLog(@"数据库打开失败");
    }

}


-(void)createTableWithName:(NSString *)tableName{
    //创建表的SQL语句
    //创建表结构：create table if not exists 表名（字段1 类型， 字段2 类型...）
    //整形：用integer 表示
    //字符串： 用text表示
    //主键约束：primary key(保证在数据库中的唯一 ，减轻冗余)
    //自增约束： autoincrement（通常在没有唯一约束的时候，我们选择用一个自增变量控制）
    NSString *creatTableSQL = [NSString stringWithFormat:@"create table if not exists %@ (number integer primary key , name text, pic text, source text)", tableName];
    //执行SQL语句 参数1：数据库对象；参数2：SQL语句
    int result = sqlite3_exec(db, creatTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
        
    }else{
        NSLog(@"创建表不成功， %d", result );
    }
}


-(void)createTableWithNameInHotModel:(NSString *)tableName{
    //创建表的SQL语句
    //创建表结构：create table if not exists 表名（字段1 类型， 字段2 类型...）
    //整形：用integer 表示
    //字符串： 用text表示
    //主键约束：primary key(保证在数据库中的唯一 ，减轻冗余)
    //自增约束： autoincrement（通常在没有唯一约束的时候，我们选择用一个自增变量控制）
    NSString *creatTableSQL = [NSString stringWithFormat:@"create table if not exists %@ (number integer primary key , name text, pic text, source text)", tableName];
    //执行SQL语句 参数1：数据库对象；参数2：SQL语句
    int result = sqlite3_exec(db, creatTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
        
    }else{
        NSLog(@"创建表不成功， %d", result );
    }

}

#pragma mar 创建历史记录第一张表
-(void)createHistoryHomeTable:(NSString *)tableName{
    NSString *creatTableSQL = [NSString stringWithFormat:@"create table if not exists %@ (number integer primary key , name text, pic text, source text)", tableName];
    //执行SQL语句 参数1：数据库对象；参数2：SQL语句
    int result = sqlite3_exec(db, creatTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
        
    }else{
        NSLog(@"创建表不成功， %d", result );
    }

}

#pragma mark 创建历史记录第二章表
-(void)createHistoryHotTable:(NSString *)tableName{
    NSString *creatTableSQL = [NSString stringWithFormat:@"create table if not exists %@ (number integer primary key , name text, pic text, source text)", tableName];
    //执行SQL语句 参数1：数据库对象；参数2：SQL语句
    int result = sqlite3_exec(db, creatTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
        
    }else{
        NSLog(@"创建表不成功， %d", result );
    }

}


#pragma mark 历史记录插入第一张表
-(void)insertHistoryHome:(CXFirstDetailModel *)model{
    NSString *insertDB = [NSString stringWithFormat:@"insert into saveHistoryFisrt (name, pic, source) values( '%@', '%@', '%@')", model.name, model.pic, model.source];
    
    
    int reslut = sqlite3_exec(db, insertDB.UTF8String, NULL, NULL, nil);
    if (reslut == SQLITE_OK) {
        //        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败%d", reslut);
    }

}

#pragma mark 历史记录第二张表
-(void)insertHistoryHot:(LDY_VoiceModel *)model{
    NSString *insertDB = [NSString stringWithFormat:@"insert into saveHistorySecond (name, pic, source) values( '%@', '%@', '%@')", model.name, model.pic, model.source];
    
    
    int reslut = sqlite3_exec(db, insertDB.UTF8String, NULL, NULL, nil);
    if (reslut == SQLITE_OK) {
        //        NSLog(@"插入成功");
    }else{
        NSLog(@"插入失败%d", reslut);
    }

}

#pragma mark 历史记录删除第一张表
-(void)deleteHistoryHome:(NSString *)name{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM saveHistoryFisrt WHERE title = '%@'",name];
    int result = sqlite3_exec(db,deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //            NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }

}

#pragma mark 历史记录删除第二张表
-(void)deleteHistoryHot:(NSString *)name{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM saveHistorySecond WHERE title = '%@'",name];
    int result = sqlite3_exec(db,deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //            NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
    
}

-(void)deleteAll{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM fightList"];
    int result = sqlite3_exec(db,deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //            NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }

}
-(void)deleteSecondTable{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM saveDetailList"];
    int result = sqlite3_exec(db,deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //            NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}
#pragma mark 删除全部历史记录第一张表
-(void)deleteHistoryHomeAll{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM saveHistoryFisrt"];
    int result = sqlite3_exec(db,deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //            NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

#pragma mark 删除全部历史记录第二张表
-(void)deleteHistoryHotAll{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM saveHistorySecond"];
    int result = sqlite3_exec(db,deleteSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        //            NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }

}

#pragma mark 查找
#pragma mark 历史记录查询第一张表
-(NSMutableArray *)selectHistoryHome{
    NSString *selectAllSql = @"select *from saveHistoryFisrt";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, selectAllSql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger number = sqlite3_column_int64(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *pic = sqlite3_column_text(stmt, 2);
            const unsigned char *source = sqlite3_column_text(stmt, 3);
            CXFirstDetailModel *model = [[CXFirstDetailModel alloc]init];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.pic = [NSString stringWithUTF8String:(const char *)pic];
            model.number = number;
            model.source = [NSString stringWithUTF8String:(const char *)source];
            [array addObject:model];
        }
    }else{
        NSLog(@"查询失败%d", result);
    }
    return  array;

}

#pragma mark 历史记录查询第二张表
-(NSMutableArray *)selectHistoryHot{
    NSString *selectAllSql = @"select *from saveHistorySecond";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, selectAllSql.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            NSInteger number = sqlite3_column_int64(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *pic = sqlite3_column_text(stmt, 2);
            const unsigned char *source = sqlite3_column_text(stmt, 3);
            LDY_VoiceModel *model = [[LDY_VoiceModel alloc]init];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            model.pic = [NSString stringWithUTF8String:(const char *)pic];
            model.number = number;
            model.source = [NSString stringWithUTF8String:(const char *)source];
            [array addObject:model];
        }
    }else{
        NSLog(@"查询失败%d", result);
    }
    return  array;
}


@end
